package com.company.corelib.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.company.corelib.model.Article;
import com.company.corelib.util.Util;

public class ArticleDAO {
	private Connection connect = null;
	private Statement statement = null;
	private PreparedStatement preparedStatement = null;
	private ResultSet rs = null;
	public List<Article> getAllArticleByCategoryId(String categoryId) {
		List<Article> articles = new ArrayList<Article>();
		try {
			connect = Util.getConnection();
			statement = connect.createStatement();
			rs = statement
			          .executeQuery("select articleid,userid,categoryid,title,summary,content,"
			          		+ "tags,image,created_time,active,likecount from tips.article where categoryid="+categoryId);
			 while (rs.next()) {
				 Article article = new Article();
				 article.setId(rs.getInt("articleid"));
				 article.setUserId(rs.getInt("userid"));
				 article.setCategoryId(rs.getInt("categoryid"));
				 article.setTitle(rs.getString("title"));
				 article.setSummary(rs.getString("summary"));
				 article.setContent(rs.getString("content"));
				 article.setTag(rs.getString("tag"));
				 article.setImage(rs.getString("image"));
				 article.setCreatedTime(rs.getDate("created_time"));
				 article.setActive(rs.getInt("active"));
				 article.setLikecount(rs.getInt("likecount"));
				 articles.add(article);
			 }
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			Util.close(connect,preparedStatement,rs);
		}
		return articles;
	}
	public String create(Article article) {
		String check = "true";
		try {
			connect = Util.getConnection();
			preparedStatement = connect
					.prepareStatement("insert into tips.article(userid,categoryid,title,summary,content,tags,image,created_time,active,likecount)"
							+" values(?,?,?,?,?,?,?,NOW(),?,?)");
			preparedStatement.setInt(1, article.getUserId());
			preparedStatement.setInt(2, article.getCategoryId());
			preparedStatement.setString(3, article.getTitle());
			preparedStatement.setString(4, article.getSummary());
			preparedStatement.setString(5, article.getContent());
			preparedStatement.setString(6, article.getTag());
			preparedStatement.setString(7, article.getImage());
			preparedStatement.setInt(8, article.getActive());
			preparedStatement.setInt(9, article.getLikecount());
			System.err.println("statement = " + preparedStatement.toString());
			preparedStatement.executeUpdate();
			check = "true";
		} catch (Exception e) {
			check = "false";
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			Util.close(connect,preparedStatement,rs);
		}
		System.err.println("check = " + check);
		return check;
	}
	public String update(Article article) {
		String check = "true";
		try {
			connect = Util.getConnection();
			preparedStatement = connect
					.prepareStatement("update tips.article set" 
							+" categoryid=?,title=?,summary=?,content=?,tags=?,image=?,active=?"
							+" where articleid=?");
			preparedStatement.setInt(1, article.getCategoryId());
			preparedStatement.setString(2, article.getTitle());
			preparedStatement.setString(3, article.getSummary());
			preparedStatement.setString(4, article.getContent());
			preparedStatement.setString(5, article.getTag());
			preparedStatement.setString(6, article.getImage());
			preparedStatement.setInt(7, article.getActive());
			preparedStatement.setInt(8, article.getId());
			preparedStatement.executeUpdate();
			check = "true";
		} catch (Exception e) {
			check = "false";
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			Util.close(connect,preparedStatement,rs);
		}
		System.err.println("Check = " + check);
		return check;
	}
	public String delete(String id) {
		String check = "true";
		try {
			connect = Util.getConnection();
			preparedStatement = connect
					.prepareStatement("delete from tips.article where articleid=?");
			preparedStatement.setString(1, id);
			preparedStatement.executeUpdate();
			check = "true";
		} catch (Exception e) {
			check = "false";
			e.printStackTrace();
		} finally {
			Util.close(connect,preparedStatement,rs);
		}
		System.err.println("Check = " + check);
		return check;
	}
	public Article getArticleById(String id) {
		Article article = new Article();
		try {
			connect = Util.getConnection();
			statement = connect.createStatement();
			rs = statement
			          .executeQuery("select articleid,userid,categoryid,title,summary,content,"
			          		+ "tags,image,created_time,active,likecount from tips.article where articleid="+id);
			 while (rs.next()) {
				 article.setId(rs.getInt("articleid"));
				 article.setUserId(rs.getInt("userid"));
				 article.setCategoryId(rs.getInt("categoryid"));
				 article.setTitle(rs.getString("title"));
				 article.setSummary(rs.getString("summary"));
				 article.setContent(rs.getString("content"));
				 article.setTag(rs.getString("tags"));
				 article.setImage(rs.getString("image"));
				 article.setCreatedTime(rs.getDate("created_time"));
				 article.setActive(rs.getInt("active"));
				 article.setLikecount(rs.getInt("likecount"));
			 }
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			Util.close(connect,preparedStatement,rs);
		}
		return article;
	}
	public List<Article> getAllArticle() {
		List<Article> articles = new ArrayList<Article>();
		try {
			connect = Util.getConnection();
			statement = connect.createStatement();
			rs = statement
			          .executeQuery("select articleid,userid,categoryid,title,summary,content,"
			          		+ "tags,image,created_time,active,likecount from tips.article");
			 while (rs.next()) {
				 Article article = new Article();
				 article.setId(rs.getInt("articleid"));
				 article.setUserId(rs.getInt("userid"));
				 article.setCategoryId(rs.getInt("categoryid"));
				 article.setTitle(rs.getString("title"));
				 article.setSummary(rs.getString("summary"));
				 article.setContent(rs.getString("content"));
				 article.setTag(rs.getString("tags"));
				 article.setImage(rs.getString("image"));
				 article.setCreatedTime(rs.getDate("created_time"));
				 article.setActive(rs.getInt("active"));
				 article.setLikecount(rs.getInt("likecount"));
				 articles.add(article);
			 }
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			Util.close(connect,preparedStatement,rs);
		}
		return articles;
	}
}
